from tkinter import *
from tkinter import ttk

import pymysql

from get_mysql import ConnectMysql
import tkinter.messagebox as messagebox


class Compile:
    def __init__(self, parent_window):
        parent_window.update()
        parent_window.destroy()
        self.window = Tk()
        self.window.title('编辑学生信息页面')
        self.window.geometry('1050x1000')
        self.frame_bottom = Frame(width=1050, height=400)
        self.frame_center = Frame(width=525, height=400)
        self.frame_top = Frame(width=525, height=400)
        self.columns = ('学号', '姓名', '性别', '年龄', '出生日期', '学院', '专业', '班级', '课程名称', '分数', '学分')
        self.tree = ttk.Treeview(self.frame_top, show="headings", height=18, columns=self.columns)
        self.vbar = ttk.Scrollbar(self.frame_top, orient=VERTICAL, command=self.tree.yview)
        self.tree.configure(yscrollcommand=self.vbar.set)
        self.tree.column('学号', width=100, anchor='center')
        self.tree.column('姓名', width=100, anchor='center')
        self.tree.column('性别', width=50, anchor='center')
        self.tree.column('年龄', width=50, anchor='center')
        self.tree.column('出生日期', width=75, anchor='center')
        self.tree.column('学院', width=150, anchor='center')
        self.tree.column('专业', width=150, anchor='center')
        self.tree.column('班级', width=150, anchor='center')
        self.tree.column('课程名称', width=100, anchor='center')
        self.tree.column('分数', width=50, anchor='center')
        self.tree.column('学分', width=50, anchor='center')
        self.tree.grid(row=0, column=0, sticky=NSEW)
        self.vbar.grid(row=0, column=1, sticky='ns')
        student_msg_list = ConnectMysql().get_student_msg_list()
        self.tree.bind('<Button-1>', self.click)
        self.s_no = []
        self.s_name = []
        self.s_sex = []
        self.s_age = []
        self.s_birthday = []
        self.d_name = []
        self.m_name = []
        self.c_name = []
        self.co_name = []
        self.s_score = []
        self.s_credit = []
        self.add_text_0 = StringVar()
        self.add_text_1 = StringVar()
        self.add_text_2 = StringVar()
        self.add_text_3 = StringVar()
        self.add_text_4 = StringVar()
        self.add_text_5 = StringVar()
        self.add_text_6 = StringVar()
        self.add_text_7 = StringVar()
        self.add_text_8 = StringVar()
        self.add_text_9 = StringVar()
        self.add_text_10 = StringVar()
        for msg_dict in student_msg_list:
            self.s_no.append(msg_dict['学号'])
            self.s_name.append(msg_dict['姓名'])
            self.s_sex.append(msg_dict['性别'])
            self.s_age.append(msg_dict['年龄'])
            self.s_birthday.append(msg_dict['出生日期'])
            self.d_name.append(msg_dict['学院'])
            self.m_name.append(msg_dict['专业'])
            self.c_name.append(msg_dict['班级'])
            self.co_name.append(msg_dict['课程名称'])
            self.s_score.append(msg_dict['分数'])
            self.s_credit.append(msg_dict['学分'])
        for i in range(len(self.s_no)):
            self.tree.insert('', i, values=(
                self.s_no[i], self.s_name[i], self.s_sex[i], self.s_age[i], self.s_birthday[i], self.d_name[i],
                self.m_name[i], self.c_name[i], self.co_name[i], self.s_score[i], self.s_credit[i]))
        for col in self.columns:
            self.tree.heading(col, text=col, command=lambda _col=col: self.tree_sort_column(self.tree, _col, False))
        self.frame_top.grid(row=0, column=0, columnspan=2)
        self.frame_center.grid(row=1, column=0)
        self.frame_bottom.grid(row=1, column=1)
        label = Label(self.frame_center, text="学生信息")
        label.grid(row=0, column=0, pady=10)
        # student_msg = {}
        for title in self.columns:
            num = self.columns.index(title)
            Label(self.frame_center, text=title + ':').grid(row=num + 1, column=0, pady=2)
            exec(f'msg_value_%s = Entry(self.frame_center, width=30, textvariable=self.add_text_%s)\n'
                 f'msg_value_%s.grid(row=%s, column=1)' % (num, num, num, num + 1))
        Button(self.frame_bottom, text="添加学生信息", width=10, command=self.add_mysql).grid(row=0, column=0, padx=20, pady=10)
        Button(self.frame_bottom, text='更新所选学生信息', width=20, command=self.update_student_msg).grid(row=1, column=0, padx=20, pady=10)
        Button(self.frame_bottom, text='删除所选学生信息', width=20, command=self.del_student_msg).grid(row=2, column=0, padx=20, pady=10)
        self.window.mainloop()

    def click(self, event):
        self.row = self.tree.identify_row(event.y)
        self.col = self.tree.identify_column(event.x)
        self.row_info = self.tree.item(self.row, "values")
        self.add_text_0.set(self.row_info[0])
        self.add_text_1.set(self.row_info[1])
        self.add_text_2.set(self.row_info[2])
        self.add_text_3.set(self.row_info[3])
        self.add_text_4.set(self.row_info[4])
        self.add_text_5.set(self.row_info[5])
        self.add_text_6.set(self.row_info[6])
        self.add_text_7.set(self.row_info[7])
        self.add_text_8.set(self.row_info[8])
        self.add_text_9.set(self.row_info[9])
        self.add_text_10.set(self.row_info[10])

    def judge_null(self):
        print(exec(f'self.add_text_%s.get()' % 0))
        for i in range(11):
            if exec(f'self.add_text_%s.get()' % i) == None:
                return False
            else:
                return True


    def tree_sort_column(self, tv, col, reverse):
        l = [(tv.set(k, col), k) for k in tv.get_children('')]
        l.sort(reverse=reverse)
        for index, (val, k) in enumerate(l):
            tv.move(k, '', index)
        tv.heading(col, command=lambda: self.tree_sort_column(tv, col, not reverse))

    def add_mysql(self):
        if self.judge_null() == True:
            try:
                db = pymysql.connect(host="localhost", user='root', password='020308', database="xjgl", charset="utf8")
                cursor = db.cursor()
                sql = """insert into student values (%s,%s,%s,%s,%s,%s,%s,%s)"""
                student_values = []
                success_values = []
                for i in range(8):
                    if i == 5:
                        d_sql = "select d_no from department where d_name=%s"
                        exec(f'cursor.execute(d_sql, self.add_text_%s.get())\n'
                             f'student_values.append(cursor.fetchone()[0])' % i)
                    elif i == 6:
                        m_sql = "select m_no from major where m_name=%s"
                        exec(f'cursor.execute(m_sql, self.add_text_%s.get())\n'
                             f'student_values.append(cursor.fetchone()[0])' % i)
                    elif i == 7:
                        c_sql = "select c_no from class where c_name=%s"
                        exec(f'cursor.execute(c_sql, self.add_text_%s.get())\n'
                             f'student_values.append(cursor.fetchone()[0])' % i)
                    else:
                        exec(f'student_values.append(self.add_text_%s.get())' % i)

                success_values.append(self.add_text_0.get())
                for i in range(3):
                    i = i + 8
                    if i == 8:
                        co_sql = "select c_no from course where co_name=%s"
                        exec(f'cursor.execute(co_sql, self.add_text_%s.get())\n'
                             f'success_values.append(cursor.fetchone()[0])' % i)
                    else:
                        exec(f'success_values.append(eval(self.add_text_%s.get()))' % i)
                try:
                    cursor.execute(sql, student_values)
                    sql = """insert into success values (%s,%s,%s,%s)"""
                    cursor.execute(sql, success_values)
                    db.commit()
                    db.close()
                    messagebox.showinfo('提示！', '插入成功！')
                    Compile(self.window)
                except:
                    db.rollback()
                    messagebox.showinfo('警告！', '检查数据格式填写是否正确！')
            except:
                messagebox.showinfo('警告！', '数据库连接失败！')
        else:
            messagebox.showinfo('警告！', '请填写学生数据')

    def update_student_msg(self):
        res = messagebox.askyesnocancel('警告！', '是否更新所填数据？')
        if res == True:
            if self.add_text_0.get() == self.row_info[0]:
                db = pymysql.connect(host="localhost", user='root', password='020308', database="xjgl", charset="utf8")
                cursor = db.cursor()
                sql = """update student set s_name = %s, s_sex = %s, s_age = %s, s_birthday = %s, d_no = %s, m_no = %s, c_no = %s where s_no = %s"""
                student_values = []
                success_values = []
                for i in range(1,8):
                    if i == 5:
                        d_sql = "select d_no from department where d_name=%s"
                        exec(f'cursor.execute(d_sql, self.add_text_%s.get())\n'
                             f'student_values.append(cursor.fetchone()[0])' % i)
                    elif i == 6:
                        m_sql = "select m_no from major where m_name=%s"
                        exec(f'cursor.execute(m_sql, self.add_text_%s.get())\n'
                             f'student_values.append(cursor.fetchone()[0])' % i)
                    elif i == 7:
                        c_sql = "select c_no from class where c_name=%s"
                        exec(f'cursor.execute(c_sql, self.add_text_%s.get())\n'
                             f'student_values.append(cursor.fetchone()[0])' % i)
                    else:
                        exec(f'student_values.append(self.add_text_%s.get())' % i)
                student_values.append(self.add_text_0.get())

                for i in range(3):
                    i = i + 8
                    if i == 8:
                        co_sql = "select c_no from course where co_name=%s"
                        exec(f'cursor.execute(co_sql, self.add_text_%s.get())\n'
                             f'success_values.append(cursor.fetchone()[0])' % i)
                    else:
                        exec(f'success_values.append(eval(self.add_text_%s.get()))' % i)
                try:
                    cursor.execute(sql, student_values)
                    sql = """update success set c_no = %s, s_score = %s, c_credit = %s where s_no = %s"""
                    success_values.append(self.add_text_0.get())
                    cursor.execute(sql, success_values)
                    db.commit()
                    messagebox.showinfo('提示！', '更新成功！')
                    Compile(self.window)
                except:
                    db.rollback()
                    messagebox.showinfo('警告！', '更新失败，数据库连接失败！')
                db.close()
            else:
                messagebox.showinfo('警告！', '不能修改学生学号！')

    def del_student_msg(self):
        res = messagebox.askyesnocancel('警告！', '是否删除所选数据？')
        if res == True:
            db = pymysql.connect(host="localhost", user='root', password='020308', database="xjgl", charset="utf8")
            cursor = db.cursor()
            try:
                sql = "delete from success where s_no = %s"
                cursor.execute(sql, self.row_info[0])
                sql = "delete from student where s_no = %s"
                cursor.execute(sql, self.row_info[0])
                db.commit()
                messagebox.showinfo('提示！', '删除成功！')
                Compile(self.window)
            except:
                db.rollback()
                messagebox.showinfo('警告！', '删除失败，数据库连接失败！')
            db.close()


window = Tk()
Compile(window)